package com.siemens.industrial.web.mvc.dao.admin;

import com.siemens.industrial.model.admin.Tenant;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Created by shuan on 2016/11/15.
 */
@Service
public class ChartsDao {
    @Resource
    JdbcTemplate jdbcTemplate;

    private static final String databaseTag = "{database}";
    private static final String getDevicesByTenantId = "select device_id as deviceId,device_unique_id as deviceName,device_type as deviceType,device_model as deviceModel from `"+databaseTag+"`.device order by device_unique_id";
    private static final String getDevicesInfo = "SELECT \n" +
            "    b.tenant_id,\n" +
            "    b.tenant_key,\n" +
            "    b.tenant_name,\n" +
            "    a.device_id,\n" +
            "    a.date_id,\n" +
            "    a.availability,\n" +
            "    a.performance,\n" +
            "    a.quality,\n" +
            "    a.oee\n" +
            "FROM\n" +
            "    (SELECT \n" +
            "        *, '42088b7a-776e-4410-b4c2-eee5b465ca73' AS databaseName\n" +
            "    FROM\n" +
            "        `42088b7a-776e-4410-b4c2-eee5b465ca73`.device_kpi\n" +
            "    WHERE\n" +
            "        (date_id = '2016-11-10') UNION ALL SELECT \n" +
            "        *, '8f671f5e-d5ad-4671-875f-ef8589e0fdb9' AS databaseName\n" +
            "    FROM\n" +
            "        `8f671f5e-d5ad-4671-875f-ef8589e0fdb9`.device_kpi\n" +
            "    WHERE\n" +
            "        (date_id = '2016-11-10') UNION ALL SELECT \n" +
            "        *, '16da638d-f99b-4e9d-96a4-35d2d6af84d1' AS databaseName\n" +
            "    FROM\n" +
            "        `16da638d-f99b-4e9d-96a4-35d2d6af84d1`.device_kpi\n" +
            "    WHERE\n" +
            "        (date_id = '2016-11-10')) a\n" +
            "        LEFT JOIN\n" +
            "    `siemens`.tenant b ON a.databaseName = b.tenant_id\n";
    private static final String getTenants = "SELECT a.tenant_id,a.tenant_name FROM `siemens`.tenant a";

    //查询租户服务续租情况(一段时间)
    private static final String findTenantServiceState = "SELECT b.service_name, a.*,\n" +
            "       case when a.start_date >= date(?) and a.start_date < date(?) then date(a.start_date)\n" +
            "               when a.start_date < date(?) and a.end_date > date(?) then date(?)\n" +
            "                else null end as startTime,\n" +
            "       case when a.start_date < date(?) and a.end_date > date(?) then date(?)\n" +
            "               when a.end_date < date(?) and a.end_date > date(?) then date(a.end_date)\n" +
            "                else null end as endTime\n" +
            "FROM\n" +
            "    `siemens`.tenant_service_relation a\n" +
            "        LEFT JOIN\n" +
            "    `siemens`.service b ON a.service_id = b.service_id\n" +
            "WHERE\n" +
            "    a.tenant_id = ?\n" +
            "        AND b.service_name = ?;";

    //查询是否在续租期限中(某天)
    private static final String findDayTenantServiceState = "select a.*,b.service_name,\n" +
            "       case when (date(?) >= date(a.start_date) and date(?) <= date(a.end_date)) then 1 else 0 end as inrange\n" +
            "from `siemens`.tenant_service_relation a \n" +
            "left join `siemens`.service b\n" +
            "on a.service_id = b.service_id\n" +
            "where a.tenant_id = ?;";

    //
    private static final String findDayTenantServiceStateByType = "select a.*,b.service_name,\n" +
            "   case when (date(?) >= date(a.start_date) and date(?) <= date(a.end_date)) then 1 else 0 end as inrange\n" +
            "from `siemens`.tenant_service_relation a \n" +
            "left join `siemens`.service b\n" +
            "on a.service_id = b.service_id\n" +
            "where a.tenant_id = ?\n" +
            "   and b.service_name = ?;";

    //获取租户所有设备
    public List getDevicesByTenantId(String database){
        return jdbcTemplate.queryForList(getDevicesByTenantId.replace(databaseTag,database));
    }

    //查询所有机床oee,availability,performance,quality所有的数据
    public List getDevicesInfo(String database){
        return jdbcTemplate.queryForList(getDevicesInfo.replace(databaseTag,database));
    }

    //查询所有租户列表
    public List<Map<String,Object>> getTenants(){
        return jdbcTemplate.queryForList(getTenants);
    }

    /**
     *查询所有机床oee或availability或performance或quality(原 可选)
     * @param tenants   租户
     * @param value 查询的数据值
     * @param date  日期
     * @param rankType  排序类型（正序 or 逆序）
     * @return  正序top10 或 逆序top10
     */
    public List getDeviceRank(List tenants,String value,String date,String rankType){
        String getDeviceRankSql = "SELECT \n" +
                "    *\n" +
                "FROM\n" +
                "    (SELECT \n" +
                "        a.date_id,\n" +
                "            a.device_id,\n" +
                "            a.indexValue,\n"+
                "            '"+((Map)tenants.get(0)).get("tenant_name")+"' tenant_name,\n" +
                "            c.device_unique_id AS device_name,\n" +
                "            c.device_model,\n" +
                "            c.device_type\n" +
                "    FROM\n" +
                "        (SELECT \n" +
                "        date_id, d.device_id, d."+value+" AS indexValue\n" +
                "    FROM\n" +
                "        `"+((Map)tenants.get(0)).get("tenant_id")+"`.device_kpi d\n" +
                "    WHERE\n" +
                "        d.date_id = '"+date+"'\n" +
                "    ORDER BY d."+value+" "+rankType+"\n" +
                "    LIMIT 0 , 10) a\n" +
                "    LEFT JOIN `"+((Map)tenants.get(0)).get("tenant_id")+"`.device c ON a.device_id = c.device_id ";

        String sqlAfter = ") f\n" +
                "ORDER BY f.indexValue "+rankType+"\n" +
                "LIMIT 0 , 10;";
        for (int i = 1;i<tenants.size();i++){
            String tenantId = (String) ((Map)tenants.get(i)).get("tenant_id");
            String tenantName = (String) ((Map)tenants.get(i)).get("tenant_name");
            String joinSql = " UNION ALL SELECT \n" +
                    "        a.date_id,\n" +
                    "            a.device_id,\n" +
                    "            a.indexValue,\n" +
                    "            '"+tenantName+"' tenant_name,\n" +
                    "            c.device_unique_id AS device_name,\n" +
                    "            c.device_model,\n" +
                    "            c.device_type\n" +
                    "    FROM\n" +
                    "        (SELECT \n" +
                    "        date_id, d.device_id, d."+value+" AS indexValue\n" +
                    "    FROM\n" +
                    "        `"+tenantId+"`.device_kpi d\n" +
                    "    WHERE\n" +
                    "        d.date_id = '"+date+"'\n" +
                    "    ORDER BY d."+value+" "+rankType+"\n" +
                    "    LIMIT 0 , 10) a\n" +
                    "    LEFT JOIN `"+tenantId+"`.device c ON a.device_id = c.device_id ";
            getDeviceRankSql += joinSql;
        }
        getDeviceRankSql += sqlAfter;

        List list = jdbcTemplate.queryForList(getDeviceRankSql);
        return list;
    }

    //查询所有机床oee或availability或performance或quality(oee=availability)
    public List findDeviceRankNew(List<Map<String,Object>> tenants,String value,String day,String rankType){
        String findDeviceRankNewSql = "select f.date_id,\n" +
                "       f.device_id,\n" +
                "       f."+value+" as indexValue,\n" +
                "       f.device_name,\n" +
                "       '"+tenants.get(0).get("tenant_name")+"' as tenant_name,\n" +
                "       f.device_model,\n" +
                "       f.device_type\n" +
                "from\n" +
                "   (select c.device_id,\n" +
                "           date('"+day+"') as date_id,\n" +
                "            c.device_unique_id AS device_name,\n" +
                "            c.device_model,\n" +
                "            c.device_type,\n" +
                "           ifnull(case when meta_definition = 1 then work_time_diff / (86400 - stop_time_diff)\n" +
                "                      when meta_definition = 0 then work_time_diff / 86400\n" +
                "                   end, 0) availability,\n" +
                "            1 performance,\n" +
                "            1 quality,\n" +
                "           ifnull(case when meta_definition = 1 then work_time_diff / (86400 - stop_time_diff)\n" +
                "                      when meta_definition = 0 then work_time_diff / 86400\n" +
                "                   end, 0) oee\n" +
                "   from ( SELECT device_id, date(start_time) date_id, m.meta_definition,\n" +
                "                sum(case when state = 1 then timestampdiff(second, start_time, end_time)\n" +
                "                      else 0 end ) work_time_diff,\n" +
                "                sum(case when state = -1 then timestampdiff(second, start_time, end_time)\n" +
                "                      else 0 end ) stop_time_diff\n" +
                "          FROM `"+tenants.get(0).get("tenant_id")+"`.device_state_period d\n" +
                "          cross join (select * from `"+tenants.get(0).get("tenant_id")+"`.metadata where meta_id = '1') m\n" +
                "          where state in (1, -1)\n" +
                "           and date(start_time) = date('"+day+"')\n" +
                "            group by d.device_id,date_id,m.meta_definition\n" +
                "       ) a\n" +
                "   right join `"+tenants.get(0).get("tenant_id")+"`.device c on a.device_id= c.device_id\n" +
                "   ORDER BY "+value+" "+rankType+"\n" +
                "   limit 0,10\n" +
                "   ) f\n";
        for(int i = 1;i<tenants.size();i++){
            String joinSql = "union all (\n" +
                    "select f.date_id,\n" +
                    "   f.device_id,\n" +
                    "   f."+value+" as indexValue,\n" +
                    "   f.device_name,\n" +
                    "   '"+tenants.get(i).get("tenant_name")+"' as tenant_name,\n" +
                    "   f.device_model,\n" +
                    "   f.device_type\n" +
                    "from\n" +
                    "(select c.device_id,\n" +
                    "       date('"+day+"') as date_id,\n" +
                    "       c.device_unique_id AS device_name,\n" +
                    "       c.device_model,\n" +
                    "       c.device_type,\n" +
                    "       ifnull(case when meta_definition = 1 then work_time_diff / (86400 - stop_time_diff)\n" +
                    "                  when meta_definition = 0 then work_time_diff / 86400\n" +
                    "               end, 0) availability,\n" +
                    "        1 performance,\n" +
                    "        1 quality,\n" +
                    "       ifnull(case when meta_definition = 1 then work_time_diff / (86400 - stop_time_diff)\n" +
                    "                  when meta_definition = 0 then work_time_diff / 86400\n" +
                    "               end, 0) oee\n" +
                    "from ( SELECT device_id, date(start_time) date_id, m.meta_definition,\n" +
                    "            sum(case when state = 1 then timestampdiff(second, start_time, end_time)\n" +
                    "                  else 0 end ) work_time_diff,\n" +
                    "            sum(case when state = -1 then timestampdiff(second, start_time, end_time)\n" +
                    "                  else 0 end ) stop_time_diff\n" +
                    "      FROM `"+tenants.get(i).get("tenant_id")+"`.device_state_period d\n" +
                    "      cross join (select * from `"+tenants.get(i).get("tenant_id")+"`.metadata where `meta_key` = 'availability') m\n" +
                    "      where state in (1, -1)\n" +
                    "       and date(start_time) = date('"+day+"')\n" +
                    "       group by d.device_id,date_id,m.meta_definition\n" +
                    "   ) a\n" +
                    "right join `"+tenants.get(i).get("tenant_id")+"`.device c on a.device_id= c.device_id\n" +
                    "ORDER BY "+value+" "+rankType+"\n" +
                    "limit 0,10\n" +
                    ") f)\n";
            findDeviceRankNewSql += joinSql;
        }
        findDeviceRankNewSql += "order by indexValue "+rankType+"\n" +
                "limit 0,10";
        return jdbcTemplate.queryForList(findDeviceRankNewSql);
    }


    public List<Map<String,Object>> getDevicesByTypeAndModel(List<Tenant> tenants,String deviceType,String deviceModel){
        String getDevicesByTypeAndModelSql = "SELECT *\n" +
                "FROM\n" +
                "    ((SELECT \n" +
                "        *,'"+tenants.get(0).getTenantId()+"' as tenantId,'"+tenants.get(0).getTenantKey()+"' as tenantKey,'"+tenants.get(0).getTenantName()+"' as tenantName\n" +
                "    FROM\n" +
                "        `"+tenants.get(0).getTenantId()+"`.device order by device_unique_id)\n";
        for (int i = 1;i<tenants.size();i++){
            String joinSql = "UNION ALL (SELECT *,'"+tenants.get(i).getTenantId()+"' as tenantId,'"+tenants.get(i).getTenantKey()+"' as tenantKey,'"+tenants.get(i).getTenantName()+"' as tenantName\n" +
                    "   FROM\n" +
                    "       `"+tenants.get(i).getTenantId()+"`.device order by device_unique_id)\n";
            getDevicesByTypeAndModelSql += joinSql;
        }

        String afterSql = ") a where a.device_type='"+deviceType+"' and a.device_model='"+deviceModel+"' order by tenantId,device_unique_id;";
        getDevicesByTypeAndModelSql += afterSql;
        return jdbcTemplate.queryForList(getDevicesByTypeAndModelSql);
    }

    //获取机床性能参数数据(oee=availability)
    public List<Map<String, Object>> getServiceCompareNew(ArrayList<Map<String,Object>> tenantAndDevice, String startTime, String endTime){
        String getServiceCompareNew = "select n.device_id, n.date_id,c.device_unique_id as deviceName, \n" +
                "      ifnull(case when meta_definition = 1 then work_time_diff / (86400 - stop_time_diff)\n" +
                "                   when meta_definition = 0 then work_time_diff / 86400\n" +
                "              end, 0) availability,\n" +
                "       1 performance,\n" +
                "       1 quality,\n" +
                "      ifnull(case when meta_definition = 1 then work_time_diff / (86400 - stop_time_diff)\n" +
                "                   when meta_definition = 0 then work_time_diff / 86400\n" +
                "              end, 0) oee       \n" +
                "from ( SELECT device_id, date(start_time) date_id, m.meta_definition,\n" +
                "             sum(case when state = 1 then timestampdiff(second, start_time, end_time)\n" +
                "                   else 0 end ) work_time_diff,\n" +
                "             sum(case when state = -1 then timestampdiff(second, start_time, end_time)\n" +
                "                   else 0 end ) stop_time_diff\n" +
                "       FROM `"+tenantAndDevice.get(0).get("tenantId")+"`.device_state_period d\n" +
                "       cross join (select * from `"+tenantAndDevice.get(0).get("tenantId")+"`.metadata where meta_id = '1') m\n" +
                "       where state in (1, -1)\n" +
                "        and device_id = '"+tenantAndDevice.get(0).get("deviceId")+"'\n" +
                "        and date(start_time) between date('"+startTime+"') and date('"+endTime+"')\n" +
                "       group by device_id,date(start_time), m.meta_definition\n" +
                "    ) a\n" +
                "right join\n" +
                "   (select date_id,'"+tenantAndDevice.get(0).get("deviceId")+"' as device_id from `"+tenantAndDevice.get(0).get("tenantId")+"`.v_num where date_id between date('"+startTime+"') and date('"+endTime+"')) n\n" +
                "on a.device_id= n.device_id\n" +
                "and a.date_id = n.date_id\n" +
                "left join `"+tenantAndDevice.get(0).get("tenantId")+"`.device c on n.device_id= c.device_id \n";

        for (int i = 1;i<tenantAndDevice.size();i++){
            String joinSql = "union all (select n.device_id, n.date_id,c.device_unique_id as deviceName,  \n" +
                    "      ifnull(case when meta_definition = 1 then work_time_diff / (86400 - stop_time_diff)\n" +
                    "                   when meta_definition = 0 then work_time_diff / 86400\n" +
                    "              end, 0) availability,\n" +
                    "       1 performance,\n" +
                    "       1 quality,\n" +
                    "      ifnull(case when meta_definition = 1 then work_time_diff / (86400 - stop_time_diff)\n" +
                    "                   when meta_definition = 0 then work_time_diff / 86400\n" +
                    "              end, 0) oee       \n" +
                    "from ( SELECT device_id, date(start_time) date_id, m.meta_definition,\n" +
                    "             sum(case when state = 1 then timestampdiff(second, start_time, end_time)\n" +
                    "                   else 0 end ) work_time_diff,\n" +
                    "             sum(case when state = -1 then timestampdiff(second, start_time, end_time)\n" +
                    "                   else 0 end ) stop_time_diff\n" +
                    "       FROM `"+tenantAndDevice.get(i).get("tenantId")+"`.device_state_period d\n" +
                    "       cross join (select * from `"+tenantAndDevice.get(i).get("tenantId")+"`.metadata where meta_id = '1') m\n" +
                    "       where state in (1, -1)\n" +
                    "        and device_id = '"+tenantAndDevice.get(i).get("deviceId")+"'\n" +
                    "        and date(start_time) between date('"+startTime+"') and date('"+endTime+"')\n" +
                    "       group by device_id,date(start_time), m.meta_definition\n" +
                    "    ) a\n" +
                    "right join\n" +
                    "   (select date_id,'"+tenantAndDevice.get(i).get("deviceId")+"' as device_id from `"+tenantAndDevice.get(i).get("tenantId")+"`.v_num where date_id between date('"+startTime+"') and date('"+endTime+"')) n\n" +
                    "on a.device_id= n.device_id\n" +
                    "and a.date_id = n.date_id\n" +
                    "left join `"+tenantAndDevice.get(i).get("tenantId")+"`.device c on n.device_id= c.device_id )\n";
            getServiceCompareNew += joinSql;
        }

        getServiceCompareNew += "order by date_id";
        return jdbcTemplate.queryForList(getServiceCompareNew);
    }

    //按小时显示OEE
    public List<Map<String,Object>> findShiftServiceNew(ArrayList<Map<String,Object>> tenantAndDevice, String day){
        String findServiceByShiftNewSql = "select l.device_id, l.date_id, l.shift_start_time, l.shift_end_time,d.device_unique_id as deviceName,\n" +
                "      case when meta_definition = 1 then work_time_diff / (shift_time_diff - stop_time_diff)\n" +
                "           when meta_definition = 0 then work_time_diff / shift_time_diff\n" +
                "      end availability,\n" +
                "       1 performance,\n" +
                "       1 quality,\n" +
                "      case when meta_definition = 1 then work_time_diff / (shift_time_diff - stop_time_diff)\n" +
                "           when meta_definition = 0 then work_time_diff / shift_time_diff\n" +
                "      end oee       \n" +
                "from (\n" +
                "   select device_id, date_id, shift_start_time, shift_end_time, \n" +
                "           timestampdiff(second, shift_start_time, shift_end_time) shift_time_diff,\n" +
                "           sum(case when state = 1 then \n" +
                "               timestampdiff(second, \n" +
                "                           if(start_time < shift_start_time, shift_start_time, start_time),\n" +
                "                           if(end_time > shift_end_time, shift_end_time, end_time))\n" +
                "               else 0 end) work_time_diff,\n" +
                "           sum(case when state = -1 then \n" +
                "               timestampdiff(second, \n" +
                "                           if(start_time < shift_start_time, shift_start_time, start_time),\n" +
                "                           if(end_time > shift_end_time, shift_end_time, end_time))\n" +
                "               else 0 end) stop_time_diff\n" +
                "   from (\n" +
                "       SELECT p.device_id, @date_id date_id, p.start_time, p.end_time, p.state, \n" +
                "              date_add(@date_id, interval ifnull(r.start_time, '00:00:00') hour_second) shift_start_time,\n" +
                "              date_add(@date_id, interval ifnull(r.end_time, '24:00:00') hour_second) shift_end_time\n" +
                "       FROM (select * from `"+tenantAndDevice.get(0).get("tenantId")+"`.device_state_period, (select @date_id := '"+day+"') d\n" +
                "               where device_id = '"+tenantAndDevice.get(0).get("deviceId")+"' \n" +
                "                 and date(start_time) = @date_id\n" +
                "           ) p\n" +
                "       cross join `"+tenantAndDevice.get(0).get("tenantId")+"`.shifts r\n" +
                "       ) a\n" +
                "   where (a.start_time <= a.shift_start_time and a.end_time > a.shift_start_time)\n" +
                "       or (a.start_time >= a.shift_start_time and a.end_time <= a.shift_end_time)\n" +
                "       or (a.start_time < a.shift_end_time and a.end_time >= a.shift_end_time)\n" +
                "   group by device_id, date_id, shift_start_time, shift_end_time,\n" +
                "           timestampdiff(second, shift_start_time, shift_end_time) \n" +
                "   ) l\n" +
                "cross join (select * from `"+tenantAndDevice.get(0).get("tenantId")+"`.metadata where meta_id = '1') m\n" +
                "left join (select * from `"+tenantAndDevice.get(0).get("tenantId")+"`.device where device_id = '"+tenantAndDevice.get(0).get("deviceId")+"' ) d \n" +
                "on l.device_id = d.device_id\n";

        for(int i = 1;i<tenantAndDevice.size();i++){
            String joinSql = "union all (select l.device_id, l.date_id, l.shift_start_time, l.shift_end_time,d.device_unique_id as deviceName,\n" +
                    "      case when meta_definition = 1 then work_time_diff / (shift_time_diff - stop_time_diff)\n" +
                    "           when meta_definition = 0 then work_time_diff / shift_time_diff\n" +
                    "      end availability,\n" +
                    "       1 performance,\n" +
                    "       1 quality,\n" +
                    "      case when meta_definition = 1 then work_time_diff / (shift_time_diff - stop_time_diff)\n" +
                    "           when meta_definition = 0 then work_time_diff / shift_time_diff\n" +
                    "      end oee\n" +
                    "from (\n" +
                    "   select device_id, date_id, shift_start_time, shift_end_time, \n" +
                    "           timestampdiff(second, shift_start_time, shift_end_time) shift_time_diff,\n" +
                    "           sum(case when state = 1 then \n" +
                    "               timestampdiff(second, \n" +
                    "                           if(start_time < shift_start_time, shift_start_time, start_time),\n" +
                    "                           if(end_time > shift_end_time, shift_end_time, end_time))\n" +
                    "               else 0 end) work_time_diff,\n" +
                    "           sum(case when state = -1 then \n" +
                    "               timestampdiff(second, \n" +
                    "                           if(start_time < shift_start_time, shift_start_time, start_time),\n" +
                    "                           if(end_time > shift_end_time, shift_end_time, end_time))\n" +
                    "               else 0 end) stop_time_diff\n" +
                    "   from (\n" +
                    "       SELECT p.device_id, @date_id date_id, p.start_time, p.end_time, p.state, \n" +
                    "              date_add(@date_id, interval ifnull(r.start_time, '00:00:00') hour_second) shift_start_time,\n" +
                    "              date_add(@date_id, interval ifnull(r.end_time, '24:00:00') hour_second) shift_end_time\n" +
                    "       FROM (select * from `"+tenantAndDevice.get(i).get("tenantId")+"`.device_state_period, (select @date_id := '"+day+"') d\n" +
                    "               where device_id = '"+tenantAndDevice.get(i).get("deviceId")+"' \n" +
                    "                 and date(start_time) = @date_id\n" +
                    "           ) p\n" +
                    "       cross join `"+tenantAndDevice.get(i).get("tenantId")+"`.shifts r\n" +
                    "       ) a\n" +
                    "   where (a.start_time <= a.shift_start_time and a.end_time > a.shift_start_time)\n" +
                    "       or (a.start_time >= a.shift_start_time and a.end_time <= a.shift_end_time)\n" +
                    "       or (a.start_time < a.shift_end_time and a.end_time >= a.shift_end_time)\n" +
                    "   group by device_id, date_id, shift_start_time, shift_end_time,\n" +
                    "           timestampdiff(second, shift_start_time, shift_end_time) \n" +
                    "   ) l\n" +
                    "cross join (select * from `"+tenantAndDevice.get(i).get("tenantId")+"`.metadata where meta_id = '1') m\n" +
                    "left join (select * from `"+tenantAndDevice.get(i).get("tenantId")+"`.device where device_id = '"+tenantAndDevice.get(i).get("deviceId")+"') d \n" +
                    "on l.device_id = d.device_id)\n";
            findServiceByShiftNewSql += joinSql;
        }
        findServiceByShiftNewSql += "order by device_id,date_id,DATE_FORMAT(shift_start_time,'%T')";
        return jdbcTemplate.queryForList(findServiceByShiftNewSql);
    }

    //查询某些设备名字
    public List<Map<String, Object>> findDeviceNameById(ArrayList<Map<String,Object>> tenantAndDevice){
        String sql = "select device_id, device_unique_id as deviceName from `"+tenantAndDevice.get(0).get("tenantId")+"`.device where device_id='"+tenantAndDevice.get(0).get("deviceId")+"'\n";
        for (int i=1;i<tenantAndDevice.size();i++){
            sql += "union all (select device_id, device_unique_id as deviceName from `"+tenantAndDevice.get(i).get("tenantId")+"`.device where device_id='"+tenantAndDevice.get(i).get("deviceId")+"'\n)";
        }
        return  jdbcTemplate.queryForList(sql);
    }

    //查询租户是否续租(一段时间)
    public List<Map<String,Object>> findTenantServiceState (String tenantId,String start_time,String end_time,String type){
        return jdbcTemplate.queryForList(findTenantServiceState,start_time,end_time,start_time,start_time,start_time,end_time,end_time,end_time,end_time,start_time,tenantId,type);
    }

    //查询是否在续租期限中(某天)
    public List<Map<String,Object>> findDayTenantServiceState(String tenantId,String day){
        return jdbcTemplate.queryForList(findDayTenantServiceState,day,day,tenantId);
    }

    //查询某服务 日期是否在服务期限内
    public List<Map<String,Object>> findDayTenantServiceStateByType(String tenantId,String day,String type){
        return jdbcTemplate.queryForList(findDayTenantServiceStateByType,day,day,tenantId,type);
    }

    //获取某班次 performance availability quality oee数据(原 可选)
    public List<Map<String,Object>> findServiceByShift(ArrayList<Map<String,Object>> tenantAndDevice, String day){
        String findServiceByShiftSql = "select a.device_id,c.device_unique_id as deviceName,a.start_time,a.end_time,\n" +
                "\tcase when b.meta_definition = '1' then ifnull(a.work_diff_time / (TIMESTAMPDIFF(SECOND, a.start_time, a.end_time) - a.stop_diff_time), 0)\n" +
                "    when b.meta_definition = '0' then ifnull(a.work_diff_time / (TIMESTAMPDIFF(SECOND, a.start_time, a.end_time)), 0)\n" +
                "    end availability,\n" +
                "    ifnull(a.product_num / (work_diff_time * prod_rate), 0) performance,\n" +
                "    ifnull(a.good_prod_num / a.product_num, 0) quality,\n" +
                "\tcase when b.meta_definition = '1' then \n" +
                "\t\tround(ifnull(a.work_diff_time / (TIMESTAMPDIFF(SECOND, a.start_time, a.end_time) - a.stop_diff_time), 0)*ifnull(a.product_num / (work_diff_time * prod_rate), 0)*ifnull(a.good_prod_num / a.product_num, 0),4)\n" +
                "    when b.meta_definition = '0' then \n" +
                "\t\tround(ifnull(a.work_diff_time / (TIMESTAMPDIFF(SECOND, a.start_time, a.end_time)), 0)*ifnull(a.product_num / (work_diff_time * prod_rate), 0)*ifnull(a.good_prod_num / a.product_num, 0),4)\n" +
                "    end oee\n" +
                "from `"+tenantAndDevice.get(0).get("tenantId")+"`.product_info a\n" +
                "join (select * from `"+tenantAndDevice.get(0).get("tenantId")+"`.metadata where meta_key = 'availability') b\n" +
                "join `"+tenantAndDevice.get(0).get("tenantId")+"`.device c on a.device_id = c.device_id\n" +
                "where a.device_id = '"+tenantAndDevice.get(0).get("deviceId")+"'\n" +
                "AND DATE(a.start_time) = date('"+day+"')\n";
        for (int i = 1;i<tenantAndDevice.size();i++){
            String joinSql = "union all\n" +
                    "(select a.device_id,c.device_unique_id as deviceName,a.start_time,a.end_time,\n" +
                    "   case when b.meta_definition = '1' then ifnull(a.work_diff_time / (TIMESTAMPDIFF(SECOND, a.start_time, a.end_time) - a.stop_diff_time), 0)\n" +
                    "    when b.meta_definition = '0' then ifnull(a.work_diff_time / (TIMESTAMPDIFF(SECOND, a.start_time, a.end_time)), 0)\n" +
                    "    end availability,\n" +
                    "    ifnull(a.product_num / (work_diff_time * prod_rate), 0) performance,\n" +
                    "    ifnull(a.good_prod_num / a.product_num, 0) quality,\n" +
                    "   case when b.meta_definition = '1' then \n" +
                    "       round(ifnull(a.work_diff_time / (TIMESTAMPDIFF(SECOND, a.start_time, a.end_time) - a.stop_diff_time), 0)*ifnull(a.product_num / (work_diff_time * prod_rate), 0)*ifnull(a.good_prod_num / a.product_num, 0),4)\n" +
                    "    when b.meta_definition = '0' then \n" +
                    "       round(ifnull(a.work_diff_time / (TIMESTAMPDIFF(SECOND, a.start_time, a.end_time)), 0)*ifnull(a.product_num / (work_diff_time * prod_rate), 0)*ifnull(a.good_prod_num / a.product_num, 0),4)\n" +
                    "    end oee\n" +
                    "from `"+tenantAndDevice.get(i).get("tenantId")+"`.product_info a\n" +
                    "join (select * from `"+tenantAndDevice.get(i).get("tenantId")+"`.metadata where meta_key = 'availability') b\n" +
                    "join `"+tenantAndDevice.get(i).get("tenantId")+"`.device c on a.device_id = c.device_id\n" +
                    "where a.device_id = '"+tenantAndDevice.get(i).get("deviceId")+"'\n" +
                    "AND DATE(a.start_time) = date('"+day+"'))\n";
            findServiceByShiftSql += joinSql;
        }
        findServiceByShiftSql+="order by start_time";
        return jdbcTemplate.queryForList(findServiceByShiftSql);
    }

    //获取某班次 performance availability quality oee数据(oee=availability)
    public List<Map<String,Object>> findServiceByShiftNew(ArrayList<Map<String,Object>> tenantAndDevice, String day){
        String findServiceByShiftNewSql = "select l.device_id, l.date_id, l.shift_start_time, l.shift_end_time, l.user_id,d.device_unique_id as deviceName,\n" +
                "      case when meta_definition = 1 then work_time_diff / (shift_time_diff - stop_time_diff)\n" +
                "           when meta_definition = 0 then work_time_diff / shift_time_diff\n" +
                "      end availability,\n" +
                "       1 performance,\n" +
                "       1 quality,\n" +
                "      case when meta_definition = 1 then work_time_diff / (shift_time_diff - stop_time_diff)\n" +
                "           when meta_definition = 0 then work_time_diff / shift_time_diff\n" +
                "      end oee       \n" +
                "from (\n" +
                "   select device_id, date_id, shift_start_time, shift_end_time, user_id, \n" +
                "           timestampdiff(second, shift_start_time, shift_end_time) shift_time_diff,\n" +
                "           sum(case when state = 1 then \n" +
                "               timestampdiff(second, \n" +
                "                           if(start_time < shift_start_time, shift_start_time, start_time),\n" +
                "                           if(end_time > shift_end_time, shift_end_time, end_time))\n" +
                "               else 0 end) work_time_diff,\n" +
                "           sum(case when state = -1 then \n" +
                "               timestampdiff(second, \n" +
                "                           if(start_time < shift_start_time, shift_start_time, start_time),\n" +
                "                           if(end_time > shift_end_time, shift_end_time, end_time))\n" +
                "               else 0 end) stop_time_diff\n" +
                "   from (\n" +
                "       SELECT p.device_id, @date_id date_id, p.start_time, p.end_time, p.state, \n" +
                "              date_add(@date_id, interval ifnull(r.start_time, '00:00:00') hour_second) shift_start_time,\n" +
                "              date_add(@date_id, interval ifnull(r.end_time, '24:00:00') hour_second) shift_end_time,\n" +
                "              r.user_id\n" +
                "       FROM (select * from `"+tenantAndDevice.get(0).get("tenantId")+"`.device_state_period, (select @date_id := '"+day+"') d\n" +
                "               where device_id = '"+tenantAndDevice.get(0).get("deviceId")+"' \n" +
                "                 and date(start_time) = @date_id\n" +
                "           ) p\n" +
                "       left join `"+tenantAndDevice.get(0).get("tenantId")+"`.device_ shift_relation r\n" +
                "       on r.begin_date <= @date_id\n" +
                "       and r.end_date >= @date_id\n" +
                "       and p.device_id = r.device_id) a\n" +
                "   where (a.start_time <= a.shift_start_time and a.end_time > a.shift_start_time)\n" +
                "       or (a.start_time >= a.shift_start_time and a.end_time <= a.shift_end_time)\n" +
                "       or (a.start_time < a.shift_end_time and a.end_time >= a.shift_end_time)\n" +
                "   group by device_id, date_id, shift_start_time, shift_end_time, user_id,\n" +
                "           timestampdiff(second, shift_start_time, shift_end_time) \n" +
                "   ) l\n" +
                "cross join (select * from `"+tenantAndDevice.get(0).get("tenantId")+"`.metadata where meta_id = '1') m\n" +
                "left join (select * from `"+tenantAndDevice.get(0).get("tenantId")+"`.device where device_id = '"+tenantAndDevice.get(0).get("deviceId")+"' ) d \n" +
                "on l.device_id = d.device_id\n";

        for(int i = 1;i<tenantAndDevice.size();i++){
            String joinSql = "union all (select l.device_id, l.date_id, l.shift_start_time, l.shift_end_time, l.user_id,d.device_unique_id as deviceName,\n" +
                    "      case when meta_definition = 1 then work_time_diff / (shift_time_diff - stop_time_diff)\n" +
                    "           when meta_definition = 0 then work_time_diff / shift_time_diff\n" +
                    "      end availability,\n" +
                    "       1 performance,\n" +
                    "       1 quality,\n" +
                    "      case when meta_definition = 1 then work_time_diff / (shift_time_diff - stop_time_diff)\n" +
                    "           when meta_definition = 0 then work_time_diff / shift_time_diff\n" +
                    "      end oee\n" +
                    "from (\n" +
                    "   select device_id, date_id, shift_start_time, shift_end_time, user_id, \n" +
                    "           timestampdiff(second, shift_start_time, shift_end_time) shift_time_diff,\n" +
                    "           sum(case when state = 1 then \n" +
                    "               timestampdiff(second, \n" +
                    "                           if(start_time < shift_start_time, shift_start_time, start_time),\n" +
                    "                           if(end_time > shift_end_time, shift_end_time, end_time))\n" +
                    "               else 0 end) work_time_diff,\n" +
                    "           sum(case when state = -1 then \n" +
                    "               timestampdiff(second, \n" +
                    "                           if(start_time < shift_start_time, shift_start_time, start_time),\n" +
                    "                           if(end_time > shift_end_time, shift_end_time, end_time))\n" +
                    "               else 0 end) stop_time_diff\n" +
                    "   from (\n" +
                    "       SELECT p.device_id, @date_id date_id, p.start_time, p.end_time, p.state, \n" +
                    "              date_add(@date_id, interval ifnull(r.start_time, '00:00:00') hour_second) shift_start_time,\n" +
                    "              date_add(@date_id, interval ifnull(r.end_time, '24:00:00') hour_second) shift_end_time,\n" +
                    "              r.user_id\n" +
                    "       FROM (select * from `"+tenantAndDevice.get(i).get("tenantId")+"`.device_state_period, (select @date_id := '"+day+"') d\n" +
                    "               where device_id = '"+tenantAndDevice.get(i).get("deviceId")+"' \n" +
                    "                 and date(start_time) = @date_id\n" +
                    "           ) p\n" +
                    "       left join `"+tenantAndDevice.get(i).get("tenantId")+"`.device_shift_relation r\n" +
                    "       on r.begin_date <= @date_id\n" +
                    "       and r.end_date >= @date_id\n" +
                    "       and p.device_id = r.device_id) a\n" +
                    "   where (a.start_time <= a.shift_start_time and a.end_time > a.shift_start_time)\n" +
                    "       or (a.start_time >= a.shift_start_time and a.end_time <= a.shift_end_time)\n" +
                    "       or (a.start_time < a.shift_end_time and a.end_time >= a.shift_end_time)\n" +
                    "   group by device_id, date_id, shift_start_time, shift_end_time, user_id,\n" +
                    "           timestampdiff(second, shift_start_time, shift_end_time) \n" +
                    "   ) l\n" +
                    "cross join (select * from `"+tenantAndDevice.get(i).get("tenantId")+"`.metadata where meta_id = '1') m\n" +
                    "left join (select * from `"+tenantAndDevice.get(i).get("tenantId")+"`.device where device_id = '"+tenantAndDevice.get(i).get("deviceId")+"') d \n" +
                    "on l.device_id = d.device_id)\n";
            findServiceByShiftNewSql += joinSql;
        }
        findServiceByShiftNewSql += "order by device_id,date_id";
        return jdbcTemplate.queryForList(findServiceByShiftNewSql);
    }

    //获取机床性能参数数据(原 可选)
    public List<Map<String, Object>> getServiceCompare(ArrayList<Map<String,Object>> tenantAndDevice, String startTime, String endTime){
        String getServiceCompareSql = "SELECT \n" +
                "    num1.*,\n" +
                "    device"+0+".deviceName,\n" +
                "    IFNULL(device_kpi.availability, 0) availability,\n" +
                "    IFNULL(device_kpi.performance, 0) performance,\n" +
                "    IFNULL(device_kpi.quality, 0) quality,\n" +
                "    IFNULL(device_kpi.oee, 0) oee\n" +
                "FROM\n" +
                "    (SELECT \n" +
                "        device_id, device_unique_id AS deviceName\n" +
                "    FROM\n" +
                "        `"+tenantAndDevice.get(0).get("tenantId")+"`.device\n" +
                "    WHERE\n" +
                "        device_id = '"+tenantAndDevice.get(0).get("deviceId")+"') device"+0+"\n" +
                "        LEFT JOIN\n" +
                "    (SELECT \n" +
                "        date_id, '"+tenantAndDevice.get(0).get("deviceId")+"' AS device_id\n" +
                "    FROM\n" +
                "        `"+tenantAndDevice.get(0).get("tenantId")+"`.v_num\n" +
                "    WHERE\n" +
                "        date_id BETWEEN DATE('"+startTime+"') AND DATE('"+endTime+"')) num1 ON device"+0+".device_id = num1.device_id\n" +
                "        LEFT JOIN\n" +
                "    `"+tenantAndDevice.get(0).get("tenantId")+"`.device_kpi ON device"+0+".device_id = device_kpi.device_id\n" +
                "        AND num1.date_id = device_kpi.date_id";
        for (int i = 1;i<tenantAndDevice.size();i++) {
            String joinSql = "        union all \n" +
                    "        SELECT \n" +
                    "    num1.*,\n" +
                    "    device"+i+".deviceName,\n" +
                    "    IFNULL(device_kpi.availability, 0) availability,\n" +
                    "    IFNULL(device_kpi.performance, 0) performance,\n" +
                    "    IFNULL(device_kpi.quality, 0) quality,\n" +
                    "    IFNULL(device_kpi.oee, 0) oee\n" +
                    "FROM\n" +
                    "    (SELECT \n" +
                    "        device_id, device_unique_id AS deviceName\n" +
                    "    FROM\n" +
                    "        `"+tenantAndDevice.get(i).get("tenantId")+"`.device\n" +
                    "    WHERE\n" +
                    "        device_id = '"+tenantAndDevice.get(i).get("deviceId")+"') device"+i+"\n" +
                    "        LEFT JOIN\n" +
                    "    (SELECT \n" +
                    "        date_id, '"+tenantAndDevice.get(i).get("deviceId")+"' AS device_id\n" +
                    "    FROM\n" +
                    "        `"+tenantAndDevice.get(i).get("tenantId")+"`.v_num\n" +
                    "    WHERE\n" +
                    "        date_id BETWEEN DATE('"+startTime+"') AND DATE('"+endTime+"')) num1 ON device"+i+".device_id = num1.device_id\n" +
                    "        LEFT JOIN\n" +
                    "    `"+tenantAndDevice.get(i).get("tenantId")+"`.device_kpi ON device"+i+".device_id = device_kpi.device_id\n" +
                    "        AND num1.date_id = device_kpi.date_id";
            getServiceCompareSql += joinSql;
        }
        return jdbcTemplate.queryForList(getServiceCompareSql);
    }
}
